<!DOCTYPE html>
<html xmlns="http://www.w3.org/1999/xhtml">
  <head>

    <meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
    <meta content="Cask Data, Inc." name="author" />
<meta content="Copyright © 2014-2017 Cask Data, Inc." name="copyright" />


    <meta name="git_release" content="6.1.1">
    <meta name="git_hash" content="05fbac36f9f7aadeb44f5728cea35136dbc243e5">
    <meta name="git_timestamp" content="2020-02-09 08:22:47 +0800">
    <title>Custom Dataset Exploration</title>

    <link rel="stylesheet" href="../_static/cdap-bootstrap.css" type="text/css" />
    <link rel="stylesheet" href="../_static/pygments.css" type="text/css" />
    <link rel="stylesheet" href="../_static/bootstrap-3.3.6/css/bootstrap.min.css" type="text/css" />
    <link rel="stylesheet" href="../_static/bootstrap-3.3.6/css/bootstrap-theme.min.css" type="text/css" />
    <link rel="stylesheet" href="../_static/css/bootstrap-sphinx.css" type="text/css" />
    <link rel="stylesheet" href="../_static/css/cdap-dynamicscrollspy-4.css" type="text/css" />
    <link rel="stylesheet" href="../_static/css/jquery.mCustomScrollbar.css" type="text/css" />
    <link rel="stylesheet" href="../_static/css/cdap-jquery.mCustomScrollbar.css" type="text/css" />
    <link rel="stylesheet" href="../_static/css/abixTreeList-2.css" type="text/css" />
    <link rel="stylesheet" href="../_static/cdap-bootstrap.css" type="text/css" />

    <script type="text/javascript">
      var DOCUMENTATION_OPTIONS = {
        URL_ROOT:    '',
        VERSION:     '6.1.1',
        COLLAPSE_INDEX: false,
        FILE_SUFFIX: '.html',
        HAS_SOURCE:  false
      };
    </script>
    <script type="text/javascript" src="../_static/jquery.js"></script>
    <script type="text/javascript" src="../_static/underscore.js"></script>
    <script type="text/javascript" src="../_static/doctools.js"></script>
    <script type="text/javascript" src="../_static/language_data.js"></script>

    <link rel="shortcut icon" href="../_static/favicon.ico"/>
    <link rel="index" title="Index" href="../genindex.html" />
    <link rel="search" title="Search" href="../search.html" />
    <link rel="top" title="Cask Data Application Platform 6.1.1 Documentation" href="../index.html" />
    <link rel="up" title="Data Exploration" href="index.html" />
    <link rel="next" title="Hive Execution Engines" href="hive-execution-engines.html" />
    <link rel="prev" title="ObjectMappedTable Exploration" href="object-mapped-tables.html" />
    <!-- block extrahead -->
    <meta charset='utf-8'>
    <meta http-equiv='X-UA-Compatible' content='IE=edge,chrome=1'>
    <meta name='viewport' content='width=device-width, initial-scale=1.0, maximum-scale=1'>
    <meta name="apple-mobile-web-app-capable" content="yes">
    <!-- block extrahead end -->

</head>
<body role="document">

<!-- block navbar -->
<div id="navbar" class="navbar navbar-inverse navbar-default navbar-fixed-top">
    <div class="container-fluid">
      <div class="row">
        <div class="navbar-header">
          <!-- .btn-navbar is used as the toggle for collapsed navbar content -->
          <a class="navbar-brand" href="../table-of-contents/../../index.html">
            <span><img alt="CDAP logo" src="../_static/cdap_logo.svg"/></span>
          </a>

          <button type="button" class="navbar-toggle" data-toggle="collapse" data-target=".nav-collapse">
            <span class="icon-bar"></span>
            <span class="icon-bar"></span>
            <span class="icon-bar"></span>
          </button>

          <div class="pull-right">
            <div class="dropdown version-dropdown">
              <a href="#" class="dropdown-toggle" data-toggle="dropdown"
                role="button" aria-haspopup="true" aria-expanded="false">
                v 6.1.1 <span class="caret"></span>
              </a>
              <ul class="dropdown-menu">
                <li><a href="//docs.cdap.io/cdap/5.1.2/en/index.html">v 5.1.2</a></li>
                <li><a href="//docs.cdap.io/cdap/4.3.4/en/index.html">v 4.3.4</a></li>
              </ul>
            </div>
          </div>
          <form class="navbar-form navbar-right navbar-search" action="../search.html" method="get">
            <div class="form-group">
              <div class="navbar-search-image material-icons"></div>
              <input type="text" name="q" class="form-control" placeholder="  Search" />
            </div>
            <input type="hidden" name="check_keywords" value="yes" />
            <input type="hidden" name="area" value="default" />
          </form>

          <div class="collapse navbar-collapse nav-collapse navbar-right navbar-navigation">
            <ul class="nav navbar-nav"><li class="docsite-nav-tab-container"><a class="docsite-nav-tab-link " href="../table-of-contents/../../index.html">简介</a></li><li class="docsite-nav-tab-container"><a class="docsite-nav-tab-link current" href="../table-of-contents/../../guides.html">手册</a></li><li class="docsite-nav-tab-container"><a class="docsite-nav-tab-link " href="../table-of-contents/../../reference-manual/index.html">参考</a></li><li class="docsite-nav-tab-container"><a class="docsite-nav-tab-link " href="../table-of-contents/../../faqs/index.html">帮助</a></li>
            </ul>
          </div>

        </div>
      </div>
    </div>
  </div><!-- block navbar end -->
<!-- block main content -->
<div class="main-container container">
  <div class="row"><div class="col-md-2">
      <div id="sidebar" class="bs-sidenav scrollable-y-outside" role="complementary">
<!-- theme_manual: developer-manual -->
<!-- theme_manual_highlight: guides -->
<!-- sidebar_title_link: ../table-of-contents/../../guides.html -->

  <div role="note" aria-label="manuals links"><h3><a href="../table-of-contents/../../guides.html">Guides</a></h3>

    <ul class="this-page-menu">
      <li class="toctree-l1"><a href="../table-of-contents/../../user-guide/index.html" rel="nofollow">用户手册</a>
      </li>
      <li class="toctree-l1"><b><a href="../table-of-contents/../../developer-manual/index.html" rel="nofollow">开发手册</a></b>
      <nav class="pagenav">
      <ul class="current">
<li class="toctree-l1"><a class="reference internal" href="../index.html"> 简介</a></li>
<li class="toctree-l1"><a class="reference internal" href="../getting-started/index.html"> 入门指南</a><ul>
<li class="toctree-l2"><a class="reference internal" href="../getting-started/sandbox/index.html">CDAP Sandbox</a><ul>
<li class="toctree-l3"><a class="reference internal" href="../getting-started/sandbox/zip.html">二进制 Zip 文件</a></li>
<li class="toctree-l3"><a class="reference internal" href="../getting-started/sandbox/zip.html#cdap-sandbox">启动和停止 CDAP Sandbox</a></li>
<li class="toctree-l3"><a class="reference internal" href="../getting-started/sandbox/virtual-machine.html">虚拟机镜像</a></li>
<li class="toctree-l3"><a class="reference internal" href="../getting-started/sandbox/docker.html">Docker 镜像</a></li>
</ul>
</li>
<li class="toctree-l2"><a class="reference internal" href="../getting-started/quick-start.html">快速入门</a></li>
<li class="toctree-l2"><a class="reference internal" href="../getting-started/dev-env.html">搭建开发环境</a></li>
<li class="toctree-l2"><a class="reference internal" href="../getting-started/start-stop-cdap.html">启动和停止 CDAP</a></li>
<li class="toctree-l2"><a class="reference internal" href="../getting-started/building-apps.html">构建并运行应用</a></li>
</ul>
</li>
<li class="toctree-l1"><a class="reference internal" href="../overview/index.html"> 概述</a><ul>
<li class="toctree-l2"><a class="reference internal" href="../overview/anatomy.html"> 大数据应用剖析</a></li>
<li class="toctree-l2"><a class="reference internal" href="../overview/modes.html"> 模式和组件</a></li>
<li class="toctree-l2"><a class="reference internal" href="../overview/abstractions.html"> 核心概念</a></li>
<li class="toctree-l2"><a class="reference internal" href="../overview/interfaces.html"> 编程接口</a></li>
</ul>
</li>
<li class="toctree-l1"><a class="reference internal" href="../building-blocks/index.html"> 抽象概念</a><ul>
<li class="toctree-l2"><a class="reference internal" href="../building-blocks/core.html"> Core Abstractions</a></li>
<li class="toctree-l2"><a class="reference internal" href="../building-blocks/applications.html"> Applications</a></li>
<li class="toctree-l2"><a class="reference internal" href="../building-blocks/datasets/index.html"> Datasets</a><ul>
<li class="toctree-l3"><a class="reference internal" href="../building-blocks/datasets/overview.html"> Overview</a></li>
<li class="toctree-l3"><a class="reference internal" href="../building-blocks/datasets/table.html"> Table API</a></li>
<li class="toctree-l3"><a class="reference internal" href="../building-blocks/datasets/fileset.html"> FileSets</a></li>
<li class="toctree-l3"><a class="reference internal" href="../building-blocks/datasets/partitioned-fileset.html"> Partitioned FileSets</a></li>
<li class="toctree-l3"><a class="reference internal" href="../building-blocks/datasets/time-partitioned-fileset.html"> TimePartitioned FileSets</a></li>
<li class="toctree-l3"><a class="reference internal" href="../building-blocks/datasets/system-custom.html"> System and Custom Datasets</a></li>
<li class="toctree-l3"><a class="reference internal" href="../building-blocks/datasets/permissions.html"> Dataset Permissions</a></li>
<li class="toctree-l3"><a class="reference internal" href="../building-blocks/datasets/cube.html"> Cube Dataset</a></li>
</ul>
</li>
<li class="toctree-l2"><a class="reference internal" href="../building-blocks/mapreduce-programs.html"> MapReduce Programs</a></li>
<li class="toctree-l2"><a class="reference internal" href="../building-blocks/plugins.html"> Plugins</a></li>
<li class="toctree-l2"><a class="reference internal" href="../building-blocks/schedules.html"> Schedules</a></li>
<li class="toctree-l2"><a class="reference internal" href="../building-blocks/secure-keys.html"> Secure Keys</a></li>
<li class="toctree-l2"><a class="reference internal" href="../building-blocks/services.html"> Services</a></li>
<li class="toctree-l2"><a class="reference internal" href="../building-blocks/spark-programs.html"> Spark Programs</a></li>
<li class="toctree-l2"><a class="reference internal" href="../building-blocks/workers.html"> Workers</a></li>
<li class="toctree-l2"><a class="reference internal" href="../building-blocks/workflows.html"> Workflows</a></li>
<li class="toctree-l2"><a class="reference internal" href="../building-blocks/artifacts.html"> Artifacts</a></li>
<li class="toctree-l2"><a class="reference internal" href="../building-blocks/program-lifecycle.html"> Program Lifecycle</a></li>
<li class="toctree-l2"><a class="reference internal" href="../building-blocks/namespaces.html"> Namespaces</a></li>
<li class="toctree-l2"><a class="reference internal" href="../building-blocks/transaction-system.html"> Transaction System</a></li>
<li class="toctree-l2"><a class="reference internal" href="../building-blocks/transactional-messaging-system.html"> Transactional Messaging System</a></li>
</ul>
</li>
<li class="toctree-l1"><a class="reference internal" href="../metadata/index.html"> 元数据</a><ul>
<li class="toctree-l2"><a class="reference internal" href="../metadata/system-metadata.html"> System Metadata</a></li>
<li class="toctree-l2"><a class="reference internal" href="../metadata/discovery-lineage.html"> Discovery and Lineage</a></li>
<li class="toctree-l2"><a class="reference internal" href="../metadata/field-lineage.html"> Field Level Lineage</a></li>
<li class="toctree-l2"><a class="reference internal" href="../metadata/audit-logging.html"> Audit Logging</a></li>
<li class="toctree-l2"><a class="reference internal" href="../metadata/metadata-ui.html"> CDAP Metadata UI</a></li>
<li class="toctree-l2"><a class="reference internal" href="../metadata/programmatic-metadata.html"> Accessing metadata programmatically</a></li>
</ul>
</li>
<li class="toctree-l1"><a class="reference internal" href="../pipelines/index.html"> 数据流管道</a><ul>
<li class="toctree-l2"><a class="reference internal" href="../pipelines/concepts-design.html"> Concepts and Design</a></li>
<li class="toctree-l2"><a class="reference internal" href="../pipelines/getting-started.html"> Getting Started</a></li>
<li class="toctree-l2"><a class="reference internal" href="../pipelines/studio.html"> CDAP Studio</a></li>
<li class="toctree-l2"><a class="reference internal" href="../pipelines/creating-pipelines.html"> Creating Pipelines</a></li>
<li class="toctree-l2"><a class="reference internal" href="../pipelines/running-pipelines.html"> Running Pipelines</a></li>
<li class="toctree-l2"><a class="reference internal" href="../pipelines/plugin-management.html"> Plugin Management</a></li>
<li class="toctree-l2"><a class="reference internal" href="../pipelines/plugins/index.html"> Plugin Reference</a><ul>
<li class="toctree-l3"><a class="reference internal" href="../pipelines/plugins/actions/index.html"> Action Plugins</a><ul class="simple">
</ul>
</li>
<li class="toctree-l3"><a class="reference internal" href="../pipelines/plugins/sources/index.html"> Source Plugins</a><ul class="simple">
</ul>
</li>
<li class="toctree-l3"><a class="reference internal" href="../pipelines/plugins/transforms/index.html"> Transform Plugins</a><ul class="simple">
</ul>
</li>
<li class="toctree-l3"><a class="reference internal" href="../pipelines/plugins/analytics/index.html"> Analytic Plugins</a><ul class="simple">
</ul>
</li>
<li class="toctree-l3"><a class="reference internal" href="../pipelines/plugins/sinks/index.html"> Sink Plugins</a><ul class="simple">
</ul>
</li>
<li class="toctree-l3"><a class="reference internal" href="../pipelines/plugins/shared-plugins/index.html"> Shared Plugins</a><ul>
<li class="toctree-l4"><a class="reference internal" href="../pipelines/plugins/shared-plugins/core.html">CoreValidator</a></li>
</ul>
</li>
<li class="toctree-l3"><a class="reference internal" href="../pipelines/plugins/post-run-plugins/index.html"> Post-run Plugins</a><ul class="simple">
</ul>
</li>
</ul>
</li>
<li class="toctree-l2"><a class="reference internal" href="../pipelines/developing-pipelines.html"> Developing Pipelines</a></li>
<li class="toctree-l2"><a class="reference internal" href="../pipelines/developing-plugins/index.html"> Developing Plugins</a><ul>
<li class="toctree-l3"><a class="reference internal" href="../pipelines/developing-plugins/plugin-basics.html">Plugin Basics</a></li>
<li class="toctree-l3"><a class="reference internal" href="../pipelines/developing-plugins/creating-a-plugin.html">Creating a Plugin</a></li>
<li class="toctree-l3"><a class="reference internal" href="../pipelines/developing-plugins/presentation-plugins.html">Plugin Presentation</a></li>
<li class="toctree-l3"><a class="reference internal" href="../pipelines/developing-plugins/testing-plugins.html">Testing Plugins</a></li>
<li class="toctree-l3"><a class="reference internal" href="../pipelines/developing-plugins/packaging-plugins.html">Packaging Plugins</a></li>
</ul>
</li>
<li class="toctree-l2"><a class="reference internal" href="../pipelines/how-cdap-pipelines-work.html"> How CDAP Pipelines Work</a></li>
</ul>
</li>
<li class="toctree-l1"><a class="reference internal" href="../cloud-runtimes/index.html"> 云平台运行</a><ul>
<li class="toctree-l2"><a class="reference internal" href="../cloud-runtimes/concepts/index.html"> Concepts</a></li>
<li class="toctree-l2"><a class="reference internal" href="../cloud-runtimes/provisioners/index.html"> Provisioners</a><ul>
<li class="toctree-l3"><a class="reference internal" href="../cloud-runtimes/provisioners/gcp-dataproc.html">Google Dataproc</a></li>
<li class="toctree-l3"><a class="reference internal" href="../cloud-runtimes/provisioners/aws-emr.html">Amazon Elastic MapReduce</a></li>
<li class="toctree-l3"><a class="reference internal" href="../cloud-runtimes/provisioners/remote-hadoop.html">Remote Hadoop</a></li>
</ul>
</li>
<li class="toctree-l2"><a class="reference internal" href="../cloud-runtimes/profiles/index.html"> Profiles</a><ul>
<li class="toctree-l3"><a class="reference internal" href="../cloud-runtimes/profiles/creating-profiles.html">Creating Profiles</a></li>
<li class="toctree-l3"><a class="reference internal" href="../cloud-runtimes/profiles/assigning-profiles.html">Assigning Profiles</a></li>
<li class="toctree-l3"><a class="reference internal" href="../cloud-runtimes/profiles/admin-controls.html">Admin Controls</a></li>
</ul>
</li>
<li class="toctree-l2"><a class="reference internal" href="../cloud-runtimes/example/index.html"> Example</a></li>
</ul>
</li>
<li class="toctree-l1"><a class="reference internal" href="../security/index.html"> 安全</a><ul>
<li class="toctree-l2"><a class="reference internal" href="../security/client-authentication.html">Client Authentication</a></li>
<li class="toctree-l2"><a class="reference internal" href="../security/cdap-authentication-clients-java.html">CDAP Authentication Client for Java</a></li>
<li class="toctree-l2"><a class="reference internal" href="../security/cdap-authentication-clients-python.html">CDAP Authentication Client for Python</a></li>
<li class="toctree-l2"><a class="reference internal" href="../security/custom-authentication.html">Custom Authentication</a></li>
<li class="toctree-l2"><a class="reference internal" href="../security/authorization-extensions.html">Authorization Extensions</a></li>
</ul>
</li>
<li class="toctree-l1"><a class="reference internal" href="../testing/index.html"> 测试和调试</a><ul>
<li class="toctree-l2"><a class="reference internal" href="../testing/testing.html"> Testing a CDAP Application</a></li>
<li class="toctree-l2"><a class="reference internal" href="../testing/debugging.html"> Debugging</a></li>
<li class="toctree-l2"><a class="reference internal" href="../testing/troubleshooting.html"> Troubleshooting</a></li>
</ul>
</li>
<li class="toctree-l1"><a class="reference internal" href="../ingesting-tools/index.html"> 数据融合</a><ul>
<li class="toctree-l2"><a class="reference internal" href="../ingesting-tools/cdap-stream-clients-java.html">CDAP Stream Client for Java</a></li>
<li class="toctree-l2"><a class="reference internal" href="../ingesting-tools/cdap-stream-clients-python.html">CDAP Stream Client for Python</a></li>
<li class="toctree-l2"><a class="reference internal" href="../ingesting-tools/cdap-stream-clients-ruby.html">CDAP Stream Client for Ruby</a></li>
<li class="toctree-l2"><a class="reference internal" href="../ingesting-tools/cdap-flume.html">CDAP Flume</a></li>
</ul>
</li>
<li class="toctree-l1 current"><a class="reference internal" href="index.html"> 数据探索</a><ul class="current">
<li class="toctree-l2"><a class="reference internal" href="filesets.html"> Fileset Exploration</a></li>
<li class="toctree-l2"><a class="reference internal" href="tables.html"> Table Exploration</a></li>
<li class="toctree-l2"><a class="reference internal" href="object-mapped-tables.html"> ObjectMappedTable Exploration</a></li>
<li class="toctree-l2 current"><a class="current reference internal" href="#"> Custom Dataset Exploration</a></li>
<li class="toctree-l2"><a class="reference internal" href="hive-execution-engines.html"> Hive Execution Engines</a></li>
</ul>
</li>
<li class="toctree-l1"><a class="reference internal" href="../advanced/index.html"> 高级主题</a><ul>
<li class="toctree-l2"><a class="reference internal" href="../advanced/application-logback.html"> Application Logback</a></li>
<li class="toctree-l2"><a class="reference internal" href="../advanced/best-practices.html"> Best Practices</a></li>
<li class="toctree-l2"><a class="reference internal" href="../advanced/class-loading.html"> Class Loading</a></li>
<li class="toctree-l2"><a class="reference internal" href="../advanced/configuring-resources.html"> Configuring Program Resources</a></li>
<li class="toctree-l2"><a class="reference internal" href="../advanced/program-retry-policies.html"> Program Retry Policies</a></li>
</ul>
</li>
</ul>
</nav>
      </li>
      <li class="toctree-l1"><a href="../table-of-contents/../../admin-manual/index.html" rel="nofollow">管理手册</a>
      </li>
      <li class="toctree-l1"><a href="../table-of-contents/../../integrations/index.html" rel="nofollow">集成手册</a>
      </li>
      <li class="toctree-l1"><a href="../table-of-contents/../../examples-manual/index.html" rel="nofollow">最佳实践</a>
      </li>
    </ul>
  </div></div>
    </div><div class="col-md-8 content" id="main-content">
    
  <div class="section" id="custom-dataset-exploration">
<span id="id1"></span><h1>Custom Dataset Exploration<a class="headerlink" href="#custom-dataset-exploration" title="Permalink to this headline">🔗</a></h1>
<p>It is often useful to be able to explore a dataset in an ad-hoc manner.
This can be done using SQL if your dataset fulfills two requirements:</p>
<ul class="simple">
<li>it defines the schema for each record; and</li>
<li>it has a method to scan its data record by record.</li>
</ul>
<p>For CDAP datasets, this is done by implementing the <code class="docutils literal notranslate"><span class="pre">RecordScannable</span></code> interface.
The CDAP built-in <code class="docutils literal notranslate"><span class="pre">KeyValueTable</span></code> and <code class="docutils literal notranslate"><span class="pre">ObjectMappedTable</span></code> datasets already implement this
and can be used for ad-hoc queries.</p>
<p>Let’s take a closer look at the <code class="docutils literal notranslate"><span class="pre">RecordScannable</span></code> interface.</p>
<div class="section" id="defining-the-record-schema">
<h2>Defining the Record Schema<a class="headerlink" href="#defining-the-record-schema" title="Permalink to this headline">🔗</a></h2>
<p>The record schema is given by returning the Java type of each record, and CDAP will derive the record schema from
that type:</p>
<div class="highlight-java notranslate"><div class="highlight"><pre><span></span><span class="n">Type</span> <span class="nf">getRecordType</span><span class="p">();</span>
</pre></div>
</div>
<p>For example, suppose you have a class <code class="docutils literal notranslate"><span class="pre">Entry</span></code> defined as:</p>
<div class="highlight-java notranslate"><div class="highlight"><pre><span></span><span class="kd">class</span> <span class="nc">Entry</span> <span class="p">{</span>
  <span class="kd">private</span> <span class="kd">final</span> <span class="n">String</span> <span class="n">key</span><span class="p">;</span>
  <span class="kd">private</span> <span class="kd">final</span> <span class="kt">int</span> <span class="n">value</span><span class="p">;</span>
  <span class="p">...</span>
<span class="p">}</span>
</pre></div>
</div>
<p>You can implement a record-scannable dataset that uses <code class="docutils literal notranslate"><span class="pre">Entry</span></code> as the record type:</p>
<div class="highlight-java notranslate"><div class="highlight"><pre><span></span><span class="kd">class</span> <span class="nc">MyDataset</span> <span class="p">...</span> <span class="kd">implements</span> <span class="n">RecordScannable</span><span class="o">&lt;</span><span class="n">Entry</span><span class="o">&gt;</span> <span class="p">{</span>
  <span class="p">...</span>
  <span class="kd">public</span> <span class="n">Type</span> <span class="nf">getRecordType</span><span class="p">()</span> <span class="p">{</span>
    <span class="k">return</span> <span class="n">Entry</span><span class="p">.</span><span class="na">class</span><span class="p">;</span>
  <span class="p">}</span>
</pre></div>
</div>
<p>Note that Java’s <code class="docutils literal notranslate"><span class="pre">Class</span></code> implements <code class="docutils literal notranslate"><span class="pre">Type</span></code> and you can simply return <code class="docutils literal notranslate"><span class="pre">Entry.class</span></code> as the record type.
CDAP will use reflection to infer a SQL-style schema using the fields of the record type.</p>
<p>In the case of the above class <code class="docutils literal notranslate"><span class="pre">Entry</span></code>, the schema will be:</p>
<div class="highlight-java notranslate"><div class="highlight"><pre><span></span><span class="p">(</span><span class="n">key</span> <span class="n">STRING</span><span class="p">,</span> <span class="n">value</span> <span class="n">INT</span><span class="p">)</span>
</pre></div>
</div>
</div>
<div class="section" id="limitations">
<span id="sql-limitations"></span><h2>Limitations<a class="headerlink" href="#limitations" title="Permalink to this headline">🔗</a></h2>
<ul>
<li><p class="first">The record type must be a structured type, that is, a Java class with fields. This is because SQL tables require
a structure type at the top level. This means the record type cannot be a primitive,
collection or map type. However, these types may appear nested inside the record type.</p>
</li>
<li><p class="first">The record type must be that of an actual Java class, not an interface. The same applies to the types of any
fields contained in the type. The reason is that interfaces only define methods but not fields; hence, reflection
would not be able to derive any fields or types from the interface.</p>
<p>The one exception to this rule is that Java collections such as <code class="docutils literal notranslate"><span class="pre">List</span></code> and <code class="docutils literal notranslate"><span class="pre">Set</span></code> are supported as well as
Java <code class="docutils literal notranslate"><span class="pre">Map</span></code>. This is possible because these interfaces are so commonly used that they deserve special handling.
These interfaces are parameterized and require special care as described in the next section.</p>
</li>
<li><p class="first">The record type must not be recursive. In other words, it cannot contain any class that directly or indirectly
contains a member of that same class. This is because a recursive type cannot be represented as a SQL schema.</p>
</li>
<li><p class="first">Fields of a class that are declared static or transient are ignored during schema generation. This means that the
record type must have at least one non-transient and non-static field. For example,
the <code class="docutils literal notranslate"><span class="pre">java.util.Date</span></code> class has only static and transient fields. Therefore a record type of <code class="docutils literal notranslate"><span class="pre">Date</span></code> is not
supported and will result in an exception when the dataset is created.</p>
</li>
<li><p class="first">A dataset can only be used in ad-hoc queries if its record type is completely contained in the dataset definition.
This means that if the record type is or contains a parameterized type, then the type parameters must be present in
the dataset definition. The reason is that the record type must be instantiated when executing an ad-hoc query.
If a type parameter depends on the jar file of the application that created the dataset, then this jar file is not
available to the query execution runtime.</p>
<p>For example, you cannot execute ad-hoc queries over an <code class="docutils literal notranslate"><span class="pre">ObjectStore&lt;MyObject&gt;</span></code> if the <code class="docutils literal notranslate"><span class="pre">MyObject</span></code> is contained in
the application jar. However, if you define your own dataset type <code class="docutils literal notranslate"><span class="pre">MyObjectStore</span></code> that extends or encapsulates an
<code class="docutils literal notranslate"><span class="pre">ObjectStore&lt;MyObject&gt;</span></code>, then <code class="docutils literal notranslate"><span class="pre">MyObject</span></code> becomes part of the dataset definition for <code class="docutils literal notranslate"><span class="pre">MyObjectStore</span></code>.</p>
</li>
</ul>
</div>
<div class="section" id="parameterized-types">
<h2>Parameterized Types<a class="headerlink" href="#parameterized-types" title="Permalink to this headline">🔗</a></h2>
<p>Suppose instead of being fixed to <code class="docutils literal notranslate"><span class="pre">String</span></code> and <code class="docutils literal notranslate"><span class="pre">int</span></code>, the <code class="docutils literal notranslate"><span class="pre">Entry</span></code> class is generic with type parameters for both
key and value:</p>
<div class="highlight-java notranslate"><div class="highlight"><pre><span></span><span class="kd">class</span> <span class="nc">GenericEntry</span><span class="o">&lt;</span><span class="n">KEY</span><span class="p">,</span> <span class="n">VALUE</span><span class="o">&gt;</span> <span class="p">{</span>
  <span class="kd">private</span> <span class="kd">final</span> <span class="n">KEY</span> <span class="n">key</span><span class="p">;</span>
  <span class="kd">private</span> <span class="kd">final</span> <span class="n">VALUE</span> <span class="n">value</span><span class="p">;</span>
  <span class="p">...</span>
<span class="p">}</span>
</pre></div>
</div>
<p>We should easily be able to implement <code class="docutils literal notranslate"><span class="pre">RecordScannable&lt;GenericEntry&lt;String,</span> <span class="pre">Integer&gt;&gt;</span></code> by defining <code class="docutils literal notranslate"><span class="pre">getRecordType()</span></code>.
However, due to Java’s runtime type erasure, returning <code class="docutils literal notranslate"><span class="pre">GenericEntry.class</span></code> does not convey complete information
about the record type. With reflection, CDAP can only determine the names of the two fields, but not their types.</p>
<p>To convey information about the type parameters, we must instead return a <code class="docutils literal notranslate"><span class="pre">ParameterizedType</span></code>, which Java’s
<code class="docutils literal notranslate"><span class="pre">Class</span></code> does not implement. An easy way is to use Guava’s <code class="docutils literal notranslate"><span class="pre">TypeToken</span></code>:</p>
<div class="highlight-java notranslate"><div class="highlight"><pre><span></span><span class="kd">class</span> <span class="nc">MyDataset</span> <span class="p">...</span> <span class="kd">implements</span> <span class="n">RecordScannable</span><span class="o">&lt;</span><span class="n">GenericEntry</span><span class="o">&lt;</span><span class="n">String</span><span class="p">,</span> <span class="n">Integer</span><span class="o">&gt;&gt;</span>
  <span class="kd">public</span> <span class="n">Type</span> <span class="nf">getRecordType</span><span class="p">()</span> <span class="p">{</span>
    <span class="k">return</span> <span class="k">new</span> <span class="n">TypeToken</span><span class="o">&lt;</span><span class="n">GenericEntry</span><span class="o">&lt;</span><span class="n">String</span><span class="p">,</span> <span class="n">Integer</span><span class="o">&gt;&gt;</span><span class="p">()</span> <span class="p">{</span> <span class="p">}.</span><span class="na">getType</span><span class="p">();</span>
  <span class="p">}</span>
</pre></div>
</div>
<p>While this seems a little more complex at first sight, it is the de-facto standard way of dealing with Java type
erasure.</p>
</div>
<div class="section" id="complex-types">
<h2>Complex Types<a class="headerlink" href="#complex-types" title="Permalink to this headline">🔗</a></h2>
<p>Your record type can also contain nested structures, lists, or maps, and they will be mapped to type names as defined in
the <a class="reference external" href="https://cwiki.apache.org/confluence/display/Hive/LanguageManual+DDL">Hive language manual</a>. For example, if
your record type is defined as:</p>
<div class="highlight-java notranslate"><div class="highlight"><pre><span></span><span class="kd">class</span> <span class="nc">Movie</span> <span class="p">{</span>
  <span class="n">String</span> <span class="n">title</span><span class="p">;</span>
  <span class="kt">int</span> <span class="n">year</span><span class="p">;</span>
  <span class="n">Map</span><span class="o">&lt;</span><span class="n">String</span><span class="p">,</span> <span class="n">String</span><span class="o">&gt;</span> <span class="n">cast</span><span class="p">;</span>
  <span class="n">List</span><span class="o">&lt;</span><span class="n">String</span><span class="o">&gt;</span> <span class="n">reviews</span><span class="p">;</span>
<span class="p">}</span>
</pre></div>
</div>
<p>The SQL schema of the dataset would be:</p>
<div class="highlight-java notranslate"><div class="highlight"><pre><span></span><span class="p">(</span><span class="n">title</span> <span class="n">STRING</span><span class="p">,</span> <span class="n">year</span> <span class="n">INT</span><span class="p">,</span> <span class="n">cast</span> <span class="n">MAP</span><span class="o">&lt;</span><span class="n">STRING</span><span class="p">,</span> <span class="n">STRING</span><span class="o">&gt;</span><span class="p">,</span> <span class="n">reviews</span> <span class="n">ARRAY</span><span class="o">&lt;</span><span class="n">STRING</span><span class="o">&gt;</span><span class="p">)</span>
</pre></div>
</div>
<p>Refer to the Hive language manual for more details on schema and data types.</p>
</div>
<div class="section" id="structuredrecord-type">
<h2>StructuredRecord Type<a class="headerlink" href="#structuredrecord-type" title="Permalink to this headline">🔗</a></h2>
<p>There are times when your record type cannot be expressed as a plain old Java object. For example, you may want to write
a custom dataset whose schema may change depending on the properties it is given. In these situations, you can implement
a record-scannable dataset that uses <code class="docutils literal notranslate"><span class="pre">StructuredRecord</span></code> as the record type:</p>
<div class="highlight-java notranslate"><div class="highlight"><pre><span></span><span class="kd">class</span> <span class="nc">MyStructuredDataset</span> <span class="p">...</span> <span class="kd">implements</span> <span class="n">RecordScannable</span><span class="o">&lt;</span><span class="n">StructuredRecord</span><span class="o">&gt;</span> <span class="p">{</span>
  <span class="p">...</span>
  <span class="kd">public</span> <span class="n">Type</span> <span class="nf">getRecordType</span><span class="p">()</span> <span class="p">{</span>
    <span class="k">return</span> <span class="n">StructuredRecord</span><span class="p">.</span><span class="na">class</span><span class="p">;</span>
  <span class="p">}</span>
</pre></div>
</div>
<p>The <code class="docutils literal notranslate"><span class="pre">StructuredRecord</span></code> class is essentially a map of fields to values, with a <code class="docutils literal notranslate"><span class="pre">Schema</span></code> describing the fields and values:</p>
<div class="highlight-java notranslate"><div class="highlight"><pre><span></span><span class="kd">public</span> <span class="kd">class</span> <span class="nc">StructuredRecord</span> <span class="p">{</span>
  <span class="p">...</span>
  <span class="kd">public</span> <span class="n">Schema</span> <span class="nf">getSchema</span><span class="p">()</span> <span class="p">{</span> <span class="p">...</span> <span class="p">}</span>

  <span class="kd">public</span> <span class="o">&lt;</span><span class="n">T</span><span class="o">&gt;</span> <span class="n">T</span> <span class="nf">get</span><span class="p">(</span><span class="n">String</span> <span class="n">fieldName</span><span class="p">)</span> <span class="p">{</span> <span class="p">...</span> <span class="p">}</span>
</pre></div>
</div>
<p>Datasets that use <code class="docutils literal notranslate"><span class="pre">StructuredRecord</span></code> as the record type must also set the schema dataset property when they are created:</p>
<div class="highlight-java notranslate"><div class="highlight"><pre><span></span><span class="nd">@Override</span>
<span class="kd">public</span> <span class="kt">void</span> <span class="nf">configure</span><span class="p">()</span> <span class="p">{</span>
  <span class="n">Schema</span> <span class="n">schema</span> <span class="o">=</span> <span class="n">Schema</span><span class="p">.</span><span class="na">recordOf</span><span class="p">(</span><span class="s">&quot;mySchema&quot;</span><span class="p">,</span>
    <span class="n">Schema</span><span class="p">.</span><span class="na">Field</span><span class="p">.</span><span class="na">of</span><span class="p">(</span><span class="s">&quot;id&quot;</span><span class="p">,</span> <span class="n">Schema</span><span class="p">.</span><span class="na">of</span><span class="p">(</span><span class="n">Schema</span><span class="p">.</span><span class="na">Type</span><span class="p">.</span><span class="na">STRING</span><span class="p">)),</span>
    <span class="n">Schema</span><span class="p">.</span><span class="na">Field</span><span class="p">.</span><span class="na">of</span><span class="p">(</span><span class="s">&quot;name&quot;</span><span class="p">,</span> <span class="n">Schema</span><span class="p">.</span><span class="na">of</span><span class="p">(</span><span class="n">Schema</span><span class="p">.</span><span class="na">Type</span><span class="p">.</span><span class="na">STRING</span><span class="p">)),</span>
    <span class="n">Schema</span><span class="p">.</span><span class="na">Field</span><span class="p">.</span><span class="na">of</span><span class="p">(</span><span class="s">&quot;age&quot;</span><span class="p">,</span> <span class="n">Schema</span><span class="p">.</span><span class="na">of</span><span class="p">(</span><span class="n">Schema</span><span class="p">.</span><span class="na">Type</span><span class="p">.</span><span class="na">INT</span><span class="p">)),</span>
    <span class="n">Schema</span><span class="p">.</span><span class="na">Field</span><span class="p">.</span><span class="na">of</span><span class="p">(</span><span class="s">&quot;email&quot;</span><span class="p">,</span> <span class="n">Schema</span><span class="p">.</span><span class="na">of</span><span class="p">(</span><span class="n">Schema</span><span class="p">.</span><span class="na">Type</span><span class="p">.</span><span class="na">STRING</span><span class="p">))</span>
  <span class="p">);</span>
  <span class="n">createDataset</span><span class="p">(</span><span class="s">&quot;users&quot;</span><span class="p">,</span> <span class="n">MyStructuredDataset</span><span class="p">.</span><span class="na">class</span><span class="p">,</span>
                <span class="n">DatasetProperties</span><span class="p">.</span><span class="na">builder</span><span class="p">()</span>
                  <span class="p">.</span><span class="na">add</span><span class="p">(</span><span class="n">DatasetProperties</span><span class="p">.</span><span class="na">SCHEMA</span><span class="p">,</span> <span class="n">schema</span><span class="p">.</span><span class="na">toString</span><span class="p">())</span>
                  <span class="p">.</span><span class="na">build</span><span class="p">());</span>
</pre></div>
</div>
<p>Failure to set the schema property will result in errors when enabling exploration on the dataset. The dataset will still
be created, but it will not be explorable until the schema property is set correctly through the RESTful API.
In addition, it is up to the user to ensure that the schema set in the dataset properties
matches the schema of records returned by the dataset. Schema mismatches will result in runtime errors.</p>
<p>The CDAP <code class="docutils literal notranslate"><span class="pre">Table</span></code> and <code class="docutils literal notranslate"><span class="pre">ObjectMappedTable</span></code> datasets implement <code class="docutils literal notranslate"><span class="pre">RecordScannable</span></code> in this way and can be used as references.</p>
</div>
<div class="section" id="scanning-records">
<span id="sql-scanning-records"></span><h2>Scanning Records<a class="headerlink" href="#scanning-records" title="Permalink to this headline">🔗</a></h2>
<p>The second requirement for enabling SQL queries over a dataset is to provide a means of scanning the dataset record
by record. Similar to how the <code class="docutils literal notranslate"><span class="pre">BatchReadable</span></code> interface makes datasets readable by MapReduce programs by iterating
over pairs of key and value, <code class="docutils literal notranslate"><span class="pre">RecordScannable</span></code> iterates over records. You need to implement a method to partition the
dataset into splits, and an additional method to create a record scanner for each split:</p>
<div class="highlight-java notranslate"><div class="highlight"><pre><span></span><span class="n">List</span><span class="o">&lt;</span><span class="n">Split</span><span class="o">&gt;</span> <span class="nf">getSplits</span><span class="p">();</span>
<span class="n">RecordScanner</span><span class="o">&lt;</span><span class="n">RECORD</span><span class="o">&gt;</span> <span class="nf">createSplitRecordScanner</span><span class="p">(</span><span class="n">Split</span> <span class="n">split</span><span class="p">);</span>
</pre></div>
</div>
<p>The <code class="docutils literal notranslate"><span class="pre">RecordScanner</span></code> is very similar to a <code class="docutils literal notranslate"><span class="pre">SplitReader</span></code>; except that instead of <code class="docutils literal notranslate"><span class="pre">nextKeyValue()</span></code>,
<code class="docutils literal notranslate"><span class="pre">getCurrentKey()</span></code>, and <code class="docutils literal notranslate"><span class="pre">getCurrentValue()</span></code>, it implements <code class="docutils literal notranslate"><span class="pre">nextRecord()</span></code> and <code class="docutils literal notranslate"><span class="pre">getCurrentRecord()</span></code>.</p>
<p>Typically, you do not implement these methods from scratch but rely on the <code class="docutils literal notranslate"><span class="pre">BatchReadable</span></code>
implementation of the underlying Tables and datasets. For example, if your dataset is backed by a <code class="docutils literal notranslate"><span class="pre">Table</span></code>:</p>
<div class="highlight-java notranslate"><div class="highlight"><pre><span></span><span class="kd">class</span> <span class="nc">MyDataset</span> <span class="kd">implements</span> <span class="n">Dataset</span><span class="p">,</span> <span class="n">RecordScannable</span><span class="o">&lt;</span><span class="n">Entry</span><span class="o">&gt;</span> <span class="p">{</span>

  <span class="kd">private</span> <span class="n">Table</span> <span class="n">table</span><span class="p">;</span>
  <span class="kd">private</span> <span class="kd">static</span> <span class="kd">final</span> <span class="kt">byte</span><span class="o">[]</span> <span class="n">VALUE_COLUMN</span> <span class="o">=</span> <span class="p">{</span> <span class="sc">&#39;c&#39;</span> <span class="p">};</span>

  <span class="c1">// ..</span>
  <span class="c1">// All other dataset methods</span>
  <span class="c1">// ...</span>

  <span class="nd">@Override</span>
  <span class="kd">public</span> <span class="n">Type</span> <span class="nf">getRecordType</span><span class="p">()</span> <span class="p">{</span>
    <span class="k">return</span> <span class="n">Entry</span><span class="p">.</span><span class="na">class</span><span class="p">;</span>
  <span class="p">}</span>

  <span class="nd">@Override</span>
  <span class="kd">public</span> <span class="n">List</span><span class="o">&lt;</span><span class="n">Split</span><span class="o">&gt;</span> <span class="nf">getSplits</span><span class="p">()</span> <span class="p">{</span>
    <span class="k">return</span> <span class="n">table</span><span class="p">.</span><span class="na">getSplits</span><span class="p">();</span>
  <span class="p">}</span>

  <span class="nd">@Override</span>
  <span class="kd">public</span> <span class="n">RecordScanner</span><span class="o">&lt;</span><span class="n">Entry</span><span class="o">&gt;</span> <span class="nf">createSplitRecordScanner</span><span class="p">(</span><span class="n">Split</span> <span class="n">split</span><span class="p">)</span> <span class="p">{</span>

    <span class="kd">final</span> <span class="n">SplitReader</span><span class="o">&lt;</span><span class="kt">byte</span><span class="o">[]</span><span class="p">,</span> <span class="n">Row</span><span class="o">&gt;</span> <span class="n">reader</span> <span class="o">=</span> <span class="n">table</span><span class="p">.</span><span class="na">createSplitReader</span><span class="p">(</span><span class="n">split</span><span class="p">);</span>

    <span class="k">return</span> <span class="k">new</span> <span class="n">RecordScanner</span><span class="o">&lt;</span><span class="n">Entry</span><span class="o">&gt;</span><span class="p">()</span> <span class="p">{</span>
      <span class="nd">@Override</span>
      <span class="kd">public</span> <span class="kt">void</span> <span class="nf">initialize</span><span class="p">(</span><span class="n">Split</span> <span class="n">split</span><span class="p">)</span> <span class="p">{</span>
        <span class="n">reader</span><span class="p">.</span><span class="na">initialize</span><span class="p">(</span><span class="n">split</span><span class="p">);</span>
      <span class="p">}</span>

      <span class="nd">@Override</span>
      <span class="kd">public</span> <span class="kt">boolean</span> <span class="nf">nextRecord</span><span class="p">()</span> <span class="p">{</span>
        <span class="k">return</span> <span class="n">reader</span><span class="p">.</span><span class="na">nextKeyValue</span><span class="p">();</span>
      <span class="p">}</span>

      <span class="nd">@Override</span>
      <span class="kd">public</span> <span class="n">Entry</span> <span class="nf">getCurrentRecord</span><span class="p">()</span>  <span class="p">{</span>
        <span class="k">return</span> <span class="k">new</span> <span class="n">Entry</span><span class="p">(</span>
          <span class="n">Bytes</span><span class="p">.</span><span class="na">toString</span><span class="p">(</span><span class="n">reader</span><span class="p">.</span><span class="na">getCurrentKey</span><span class="p">()),</span>
          <span class="n">reader</span><span class="p">.</span><span class="na">getCurrentValue</span><span class="p">().</span><span class="na">getInt</span><span class="p">(</span><span class="n">VALUE_COLUMN</span><span class="p">));</span>
      <span class="p">}</span>

      <span class="nd">@Override</span>
      <span class="kd">public</span> <span class="kt">void</span> <span class="nf">close</span><span class="p">()</span> <span class="p">{</span>
        <span class="n">reader</span><span class="p">.</span><span class="na">close</span><span class="p">();</span>
      <span class="p">}</span>

    <span class="p">}</span>
  <span class="p">}</span>
<span class="p">}</span>
</pre></div>
</div>
<p>While this is straightforward, it is even easier if your dataset already implements <code class="docutils literal notranslate"><span class="pre">BatchReadable</span></code>.
In that case, you can reuse its implementation of <code class="docutils literal notranslate"><span class="pre">getSplits()</span></code> and implement the split record scanner
with a helper method
(<code class="docutils literal notranslate"><span class="pre">Scannables.splitRecordScanner</span></code>) already defined by CDAP. It takes a split reader and a <code class="docutils literal notranslate"><span class="pre">RecordMaker</span></code>
that transforms a key and value, as produced by the <code class="docutils literal notranslate"><span class="pre">BatchReadable</span></code>’s split reader,
into a record:</p>
<div class="highlight-java notranslate"><div class="highlight"><pre><span></span><span class="nd">@Override</span>
<span class="kd">public</span> <span class="n">RecordScanner</span><span class="o">&lt;</span><span class="n">Entry</span><span class="o">&gt;</span> <span class="nf">createSplitRecordScanner</span><span class="p">(</span><span class="n">Split</span> <span class="n">split</span><span class="p">)</span> <span class="p">{</span>
  <span class="k">return</span> <span class="n">Scannables</span><span class="p">.</span><span class="na">splitRecordScanner</span><span class="p">(</span>
    <span class="n">table</span><span class="p">.</span><span class="na">createSplitReader</span><span class="p">(</span><span class="n">split</span><span class="p">),</span>
    <span class="k">new</span> <span class="n">Scannables</span><span class="p">.</span><span class="na">RecordMaker</span><span class="o">&lt;</span><span class="kt">byte</span><span class="o">[]</span><span class="p">,</span> <span class="n">Row</span><span class="p">,</span> <span class="n">Entry</span><span class="o">&gt;</span><span class="p">()</span> <span class="p">{</span>
      <span class="nd">@Override</span>
      <span class="kd">public</span> <span class="n">Entry</span> <span class="nf">makeRecord</span><span class="p">(</span><span class="kt">byte</span><span class="o">[]</span> <span class="n">key</span><span class="p">,</span> <span class="n">Row</span> <span class="n">row</span><span class="p">)</span> <span class="p">{</span>
        <span class="k">return</span> <span class="k">new</span> <span class="n">Entry</span><span class="p">(</span><span class="n">Bytes</span><span class="p">.</span><span class="na">toString</span><span class="p">(</span><span class="n">key</span><span class="p">),</span> <span class="n">row</span><span class="p">.</span><span class="na">getInt</span><span class="p">(</span><span class="n">VALUE_COLUMN</span><span class="p">));</span>
      <span class="p">}</span>
    <span class="p">});</span>
<span class="p">}</span>
</pre></div>
</div>
<p>Note there is an even simpler helper (<code class="docutils literal notranslate"><span class="pre">Scannables.valueRecordScanner</span></code>) that derives a split
record scanner from a split reader. For each key and value returned by the split reader it ignores the key
and returns the value. For example,
if your dataset implements <code class="docutils literal notranslate"><span class="pre">BatchReadable&lt;String,</span> <span class="pre">Employee&gt;</span></code>, then you can implement <code class="docutils literal notranslate"><span class="pre">RecordScannable&lt;Employee&gt;</span></code> by
defining:</p>
<div class="highlight-java notranslate"><div class="highlight"><pre><span></span><span class="nd">@Override</span>
<span class="kd">public</span> <span class="n">RecordScanner</span><span class="o">&lt;</span><span class="n">Employee</span><span class="o">&gt;</span> <span class="nf">createSplitRecordScanner</span><span class="p">(</span><span class="n">Split</span> <span class="n">split</span><span class="p">)</span> <span class="p">{</span>
  <span class="k">return</span> <span class="n">Scannables</span><span class="p">.</span><span class="na">valueRecordScanner</span><span class="p">(</span><span class="n">table</span><span class="p">.</span><span class="na">createSplitReader</span><span class="p">(</span><span class="n">split</span><span class="p">));</span>
<span class="p">}</span>
</pre></div>
</div>
<p>An example demonstrating an implementation of <code class="docutils literal notranslate"><span class="pre">RecordScannable</span></code> is included in the CDAP Sandbox in the
directory <code class="docutils literal notranslate"><span class="pre">examples/Purchase</span></code>, namely the <code class="docutils literal notranslate"><span class="pre">PurchaseHistoryStore</span></code>.</p>
</div>
<div class="section" id="writing-to-datasets-with-sql">
<h2>Writing to Datasets with SQL<a class="headerlink" href="#writing-to-datasets-with-sql" title="Permalink to this headline">🔗</a></h2>
<p>Data can be inserted into datasets using SQL. For example, you can write to a dataset named
<code class="docutils literal notranslate"><span class="pre">ProductCatalog</span></code> with this SQL query:</p>
<div class="highlight-java notranslate"><div class="highlight"><pre><span></span><span class="n">INSERT</span> <span class="n">INTO</span> <span class="n">TABLE</span> <span class="n">dataset_productcatalog</span> <span class="n">SELECT</span> <span class="p">...</span>
</pre></div>
</div>
<p>In order for a dataset to enable record insertion from SQL query, it simply has to expose a way to write records
into itself.</p>
<p>For CDAP datasets, this is done by implementing the <code class="docutils literal notranslate"><span class="pre">RecordWritable</span></code> interface.
The system dataset KeyValueTable already implements this and can be used to insert records from SQL queries.</p>
<p>Let’s take a closer look at the <code class="docutils literal notranslate"><span class="pre">RecordWritable</span></code> interface.</p>
<div class="section" id="id2">
<h3>Defining the Record Schema<a class="headerlink" href="#id2" title="Permalink to this headline">🔗</a></h3>
<p>Just like in the <code class="docutils literal notranslate"><span class="pre">RecordScannable</span></code> interface, the record schema is given by returning the Java type of each record,
using the method:</p>
<div class="highlight-java notranslate"><div class="highlight"><pre><span></span><span class="n">Type</span> <span class="nf">getRecordType</span><span class="p">();</span>
</pre></div>
</div>
<p><a class="reference internal" href="#sql-limitations"><span class="std std-ref">The same rules</span></a> that apply to the type of the <code class="docutils literal notranslate"><span class="pre">RecordScannable</span></code> interface apply
to the type of the <code class="docutils literal notranslate"><span class="pre">RecordWritable</span></code> interface. In fact, if a dataset implements both <code class="docutils literal notranslate"><span class="pre">RecordScannable</span></code> and
<code class="docutils literal notranslate"><span class="pre">RecordWritable</span></code> interfaces, they will have to use identical record types.</p>
</div>
<div class="section" id="writing-records">
<h3>Writing Records<a class="headerlink" href="#writing-records" title="Permalink to this headline">🔗</a></h3>
<p>To enable inserting SQL query results, a dataset needs to provide a means of writing a record into itself.
This is similar to how the <code class="docutils literal notranslate"><span class="pre">BatchWritable</span></code> interface makes datasets writable from MapReduce programs by providing
a way to write pairs of key and value. You need to implement the <code class="docutils literal notranslate"><span class="pre">RecordWritable</span></code> method:</p>
<div class="highlight-java notranslate"><div class="highlight"><pre><span></span><span class="kt">void</span> <span class="nf">write</span><span class="p">(</span><span class="n">RECORD</span> <span class="n">record</span><span class="p">)</span> <span class="kd">throws</span> <span class="n">IOException</span><span class="p">;</span>
</pre></div>
</div>
<p>Continuing the <em>MyDataset</em> <a class="reference internal" href="#sql-scanning-records"><span class="std std-ref">example used above</span></a>, which showed an implementation of
<code class="docutils literal notranslate"><span class="pre">RecordScannable</span></code>, this example an implementation of a <code class="docutils literal notranslate"><span class="pre">RecordWritable</span></code> dataset that is backed by a <code class="docutils literal notranslate"><span class="pre">Table</span></code>:</p>
<div class="highlight-java notranslate"><div class="highlight"><pre><span></span><span class="kd">class</span> <span class="nc">MyDataset</span> <span class="kd">implements</span> <span class="n">Dataset</span><span class="p">,</span> <span class="p">...,</span> <span class="n">RecordWritable</span><span class="o">&lt;</span><span class="n">Entry</span><span class="o">&gt;</span> <span class="p">{</span>

  <span class="kd">private</span> <span class="n">Table</span> <span class="n">table</span><span class="p">;</span>
  <span class="kd">private</span> <span class="kd">static</span> <span class="kd">final</span> <span class="kt">byte</span><span class="o">[]</span> <span class="n">VALUE_COLUMN</span> <span class="o">=</span> <span class="p">{</span> <span class="sc">&#39;c&#39;</span> <span class="p">};</span>

  <span class="c1">// ..</span>
  <span class="c1">// All other dataset methods</span>
  <span class="c1">// ...</span>

  <span class="nd">@Override</span>
  <span class="kd">public</span> <span class="n">Type</span> <span class="nf">getRecordType</span><span class="p">()</span> <span class="p">{</span>
    <span class="k">return</span> <span class="n">Entry</span><span class="p">.</span><span class="na">class</span><span class="p">;</span>
  <span class="p">}</span>

  <span class="nd">@Override</span>
  <span class="kd">public</span> <span class="kt">void</span> <span class="nf">write</span><span class="p">(</span><span class="n">Entry</span> <span class="n">record</span><span class="p">)</span> <span class="kd">throws</span> <span class="n">IOException</span> <span class="p">{</span>
    <span class="k">return</span> <span class="n">table</span><span class="p">.</span><span class="na">put</span><span class="p">(</span><span class="n">Bytes</span><span class="p">.</span><span class="na">toBytes</span><span class="p">(</span><span class="n">record</span><span class="p">.</span><span class="na">getKey</span><span class="p">()),</span> <span class="n">VALUE_COLUMN</span><span class="p">,</span> <span class="n">Bytes</span><span class="p">.</span><span class="na">toBytes</span><span class="p">(</span><span class="n">record</span><span class="p">.</span><span class="na">getValue</span><span class="p">()));</span>
  <span class="p">}</span>
<span class="p">}</span>
</pre></div>
</div>
<p>Note that a dataset can implement either <code class="docutils literal notranslate"><span class="pre">RecordScannable</span></code>, <code class="docutils literal notranslate"><span class="pre">RecordWritable</span></code>, or both.</p>
</div>
</div>
<div class="section" id="formulating-queries">
<h2>Formulating Queries<a class="headerlink" href="#formulating-queries" title="Permalink to this headline">🔗</a></h2>
<p>When creating your queries, keep these limitations in mind:</p>
<ul class="simple">
<li>The query syntax of CDAP is a subset of the variant of SQL that was first defined by Apache Hive.</li>
<li>The SQL commands <code class="docutils literal notranslate"><span class="pre">UPDATE</span></code> and <code class="docutils literal notranslate"><span class="pre">DELETE</span></code> are not allowed on CDAP datasets.</li>
<li>When addressing your datasets in queries, you need to prefix the dataset name with
<code class="docutils literal notranslate"><span class="pre">dataset_</span></code>. For example, if your dataset is named <code class="docutils literal notranslate"><span class="pre">ProductCatalog</span></code>, then the
corresponding table name is <code class="docutils literal notranslate"><span class="pre">dataset_productcatalog</span></code>. Note that the table name is
lower-case.</li>
<li>If your dataset name contains a ‘.’ or a ‘-‘, those characters will be converted to ‘_’ for the Hive
table name. For example, if your dataset is named <code class="docutils literal notranslate"><span class="pre">my-table.name</span></code>, the corresponding Hive table
name will be <code class="docutils literal notranslate"><span class="pre">dataset_my_table_name</span></code>. Beware of name collisions. For example, <code class="docutils literal notranslate"><span class="pre">my.table</span></code> will
use the same Hive table name as <code class="docutils literal notranslate"><span class="pre">my_table</span></code>.</li>
<li>You can also configure the table name by setting the dataset property <code class="docutils literal notranslate"><span class="pre">explore.table.name</span></code>
(see <a class="reference internal" href="index.html#data-exploration"><span class="std std-ref">Data Exploration</span></a>).</li>
</ul>
<p>For more examples of queries, please refer to the <a class="reference external" href="https://cwiki.apache.org/confluence/display/Hive/LanguageManual+DML#LanguageManualDML-InsertingdataintoHiveTablesfromqueries">Hive language manual</a>.</p>
</div>
</div>

</div>
    <div class="col-md-2">
      <div id="right-sidebar" class="bs-sidenav scrollable-y" role="complementary">
        <div id="localtoc-scrollspy">
        </div>
      </div>
    </div></div>
</div>
<!-- block main content end -->
<!-- block footer -->
<footer class="footer">
      <div class="container">
        <div class="row">
          <div class="col-md-2 footer-left"><a title="ObjectMappedTable Exploration" href="object-mapped-tables.html" />Previous</a></div>
          <div class="col-md-8 footer-center"><a class="footer-tab-link" href="../table-of-contents/../../reference-manual/licenses/index.html">Copyright</a> &copy; 2014-2020 Cask Data, Inc.&bull; <a class="footer-tab-link" href="//docs.cask.co/cdap/6.1.1/cdap-docs-6.1.1-web.zip" rel="nofollow">Download</a> an archive or
<a class="footer-tab-link" href="//docs.cask.co/cdap">switch the version</a> of the documentation
          </div>
          <div class="col-md-2 footer-right"><a title="Hive Execution Engines" href="hive-execution-engines.html" />Next</a></div>
        </div>
      </div>
    </footer>
<!-- block footer end -->
<script type="text/javascript" src="../_static/bootstrap-3.3.6/js/bootstrap.min.js"></script><script type="text/javascript" src="../_static/js/bootstrap-sphinx.js"></script><script type="text/javascript" src="../_static/js/abixTreeList-2.js"></script><script type="text/javascript" src="../_static/js/cdap-dynamicscrollspy-4.js"></script><script type="text/javascript" src="../_static/js/cdap-version-menu.js"></script><script type="text/javascript" src="../_static/js/copy-to-clipboard.js"></script><script type="text/javascript" src="../_static/js/jquery.mousewheel.min.js"></script><script type="text/javascript" src="../_static/js/jquery.mCustomScrollbar.js"></script><script type="text/javascript" src="../_static/js/js.cookie.js"></script><script type="text/javascript" src="../_static/js/tabbed-parsed-literal-0.2.js"></script><script type="text/javascript" src="../_static/js/cdap-onload-javascript.js"></script><script type="text/javascript" src="../_static/js/cdap-version-menu.js"></script>
    <script src="https://cdap.gitee.io/docs/cdap/json-versions.js"/></script>
  </body>
</html>